using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectManyModule_Ribbon(SqlGuid SessionGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string selectManyModuleRibbonCommandText =
                @"
                    SELECT 
	                    RibbonGuid, RibbonId, RibbonName, RibbonOrder 

                    FROM 
	                    Module_Ribbon

                    WHERE 
	                    ApplicationGuid = 
	                    (
		                    SELECT
			                    ApplicationGuid

		                    FROM 
			                    Security_Session SS

		                    WHERE 
			                    SessionGuid = @SessionGuid
	                    )

                    AND
	                    RibbonGuid IN 
	                    (
		                    SELECT
			                    SRR.RibbonGuid

		                    FROM 
			                    Security_Session SS

		                    INNER JOIN
			                    Security_UserRole SUR
			                    ON SS.UserGuid = SUR.UserGuid

		                    INNER JOIN
			                    Security_RibbonRole SRR
			                    ON SUR.RoleGuid = SRR.RoleGuid

		                    WHERE 
			                    SessionGuid = @SessionGuid
	                    )

                    ORDER BY 
	                    RibbonOrder ASC
                ";

                SqlCommand selectManyModuleRibbonCommand = new SqlCommand(selectManyModuleRibbonCommandText, connection);

                selectManyModuleRibbonCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));

                SqlContext.Pipe.ExecuteAndSend(selectManyModuleRibbonCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
